from typing import Optional, List, Dict, Any
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select, and_, or_
from sqlalchemy.orm import selectinload, joinedload

from app.core.security import get_password_hash, verify_password
from app.models.user import User, Role, Dept, Post, sys_user_role, sys_role_menu
from app.models.menu import Menu
from app.services.base import BaseService


class UserService(BaseService[User]):
    """用户服务"""

    def __init__(self):
        super().__init__(User)

    async def get_by_username(
        self, db: AsyncSession, *, username: str, tenant_id: Optional[str] = None
    ) -> Optional[User]:
        """根据用户名获取用户"""
        query = select(User).where(
            User.user_name == username,
            User.del_flag == '0'
        )

        if tenant_id:
            query = query.where(User.tenant_id == tenant_id)

        result = await db.execute(query)
        return result.scalar_one_or_none()

    async def get_by_email(
        self, db: AsyncSession, *, email: str, tenant_id: Optional[str] = None
    ) -> Optional[User]:
        """根据邮箱获取用户"""
        query = select(User).where(
            User.email == email,
            User.del_flag == '0'
        )

        if tenant_id:
            query = query.where(User.tenant_id == tenant_id)

        result = await db.execute(query)
        return result.scalar_one_or_none()

    async def get_with_roles(
        self, db: AsyncSession, *, user_id: int, tenant_id: Optional[str] = None
    ) -> Optional[User]:
        """获取用户及其角色信息"""
        query = select(User).options(
            selectinload(User.roles)
        ).where(
            User.user_id == user_id,
            User.del_flag == '0'
        )

        if tenant_id:
            query = query.where(User.tenant_id == tenant_id)

        result = await db.execute(query)
        return result.scalar_one_or_none()

    async def authenticate(
        self, db: AsyncSession, *, username: str, password: str, tenant_id: Optional[str] = None
    ) -> Optional[User]:
        """用户认证"""
        user = await self.get_by_username(db, username=username, tenant_id=tenant_id)
        if not user:
            return None
        if not verify_password(password, user.password):
            return None
        return user

    async def create_user(
        self, db: AsyncSession, *, obj_in: Dict[str, Any], tenant_id: str
    ) -> User:
        """创建用户"""
        # 处理密码
        if "password" in obj_in:
            obj_in["password"] = get_password_hash(obj_in["password"])

        # 提取角色ID
        role_ids = obj_in.pop("role_ids", [])

        # 创建用户
        user = await self.create(db, obj_in=obj_in, tenant_id=tenant_id)

        # 分配角色
        if role_ids:
            await self.assign_roles(db, user_id=user.id, role_ids=role_ids, tenant_id=tenant_id)

        return user

    async def update_user(
        self, db: AsyncSession, *, db_obj: User, obj_in: Dict[str, Any]
    ) -> User:
        """更新用户"""
        # 处理密码
        if "password" in obj_in:
            obj_in["password"] = get_password_hash(obj_in["password"])

        # 提取角色ID
        role_ids = obj_in.pop("role_ids", None)

        # 更新用户基本信息
        user = await self.update(db, db_obj=db_obj, obj_in=obj_in)

        # 更新角色分配
        if role_ids is not None:
            await self.assign_roles(db, user_id=user.id, role_ids=role_ids, tenant_id=user.tenant_id)

        return user

    async def assign_roles(
        self, db: AsyncSession, *, user_id: int, role_ids: List[int], tenant_id: str
    ):
        """分配角色给用户"""
        # 删除现有角色关联
        await db.execute(
            user_role_association.delete().where(
                user_role_association.c.user_id == user_id
            )
        )

        # 添加新角色关联
        if role_ids:
            values = [
                {"user_id": user_id, "role_id": role_id, "tenant_id": tenant_id}
                for role_id in role_ids
            ]
            await db.execute(user_role_association.insert().values(values))

        await db.commit()

    async def get_user_roles(
        self, db: AsyncSession, *, user_id: int, tenant_id: str
    ) -> List[Role]:
        """获取用户的角色列表"""
        query = select(Role).join(
            user_role_association,
            Role.id == user_role_association.c.role_id
        ).where(
            and_(
                user_role_association.c.user_id == user_id,
                user_role_association.c.tenant_id == tenant_id,
                Role.is_deleted == False
            )
        )

        result = await db.execute(query)
        return result.scalars().all()

    async def get_user_permissions(
        self, db: AsyncSession, *, user_id: int, tenant_id: str
    ):
        """获取用户的权限列表"""
        # 获取用户信息，包含角色
        user = await self.get_with_roles(db, user_id=user_id, tenant_id=tenant_id)
        if not user:
            return []

        # 如果是超级管理员，返回所有权限
        if user.is_superuser:
            query = select(Permission.perms).where(
                and_(
                    Permission.tenant_id == tenant_id,
                    Permission.is_deleted == False,
                    Permission.status == True,
                    Permission.perms.isnot(None),
                    Permission.perms != ''
                )
            )
            result = await db.execute(query)
            return [perm for perm in result.scalars().all() if perm]

        # 获取用户角色ID列表
        role_ids = [role.id for role in user.roles]
        if not role_ids:
            return []

        # 查询角色权限
        query = (
            select(Permission.perms)
            .join(
                role_permission_association,
                Permission.id == role_permission_association.c.permission_id
            )
            .where(
                and_(
                    role_permission_association.c.role_id.in_(role_ids),
                    role_permission_association.c.tenant_id == tenant_id,
                    Permission.tenant_id == tenant_id,
                    Permission.is_deleted == False,
                    Permission.status == True,
                    Permission.perms.isnot(None),
                    Permission.perms != ''
                )
            )
            .distinct()
        )

        result = await db.execute(query)
        return [perm for perm in result.scalars().all() if perm]

    async def get_users_with_pagination(
        self, db: AsyncSession, *, tenant_id: str, page: int = 1, page_size: int = 10, filters: dict = None
    ) -> tuple:
        """分页获取用户列表"""
        from sqlalchemy import func, select, and_, or_

        # 构建基础查询，只查询用户基础数据
        query = select(User).where(
            and_(
                User.tenant_id == tenant_id,
                User.is_deleted == False
            )
        )

        # 应用过滤条件
        if filters:
            if filters.get('keyword'):
                keyword = f"%{filters['keyword']}%"
                query = query.where(
                    or_(
                        User.username.like(keyword),
                        User.nickname.like(keyword),
                        User.email.like(keyword)
                    )
                )

            if filters.get('dept_id') is not None:
                query = query.where(User.dept_id == filters['dept_id'])

            if filters.get('status') is not None:
                query = query.where(User.status == filters['status'])

        # 获取总数（不使用预加载来提高性能）
        count_query = select(func.count(User.id)).where(
            and_(
                User.tenant_id == tenant_id,
                User.is_deleted == False
            )
        )

        # 对count查询也应用过滤条件
        if filters:
            if filters.get('keyword'):
                keyword = f"%{filters['keyword']}%"
                count_query = count_query.where(
                    or_(
                        User.username.like(keyword),
                        User.nickname.like(keyword),
                        User.email.like(keyword)
                    )
                )

            if filters.get('dept_id') is not None:
                count_query = count_query.where(User.dept_id == filters['dept_id'])

            if filters.get('status') is not None:
                count_query = count_query.where(User.status == filters['status'])

        total_result = await db.execute(count_query)
        total = total_result.scalar()

        # 分页查询
        offset = (page - 1) * page_size
        query = query.offset(offset).limit(page_size).order_by(User.created_at.desc())

        result = await db.execute(query)
        users = result.scalars().all()

        # 转换为响应格式，只包含用户基础数据
        user_list = []
        for user in users:
            user_dict = {
                "userId": user.user_id,
                "userName": user.user_name,
                "nickName": user.nick_name,
                "email": user.email,
                "phonenumber": user.phonenumber,
                "avatar": user.avatar,
                "sex": user.sex,
                "status": user.status,
                "userType": user.user_type,
                "loginDate": user.login_date,
                "loginIp": user.login_ip,
                "deptId": user.dept_id,
                "remark": getattr(user, 'remark', None),
                "createTime": user.create_time.isoformat() if user.create_time else None,
                "updateTime": user.update_time.isoformat() if user.update_time else None,
                "tenantId": user.tenant_id
            }
            user_list.append(user_dict)

        return user_list, total

    async def get_user_detail(
        self, db: AsyncSession, *, user_id: int, tenant_id: str
    ):
        """获取用户详情"""
        query = select(User).options(
            selectinload(User.dept),
            selectinload(User.roles)
        ).where(
            and_(
                User.id == user_id,
                User.tenant_id == tenant_id,
                User.is_deleted == False
            )
        )

        result = await db.execute(query)
        user = result.scalar_one_or_none()

        if not user:
            return None

        return {
            "id": user.id,
            "username": user.username,
            "nickname": user.nickname,
            "email": user.email,
            "phone": user.phone,
            "avatar": user.avatar,
            "gender": user.gender,
            "status": user.status,
            "is_superuser": user.is_superuser,
            "is_active": user.is_active,
            "last_login_time": user.last_login_time,
            "last_login_ip": user.last_login_ip,
            "dept_id": user.dept_id,
            "dept_name": user.dept.name if user.dept else None,
            "role_names": [role.name for role in user.roles] if user.roles else [],
            "remark": user.remark,
            "created_at": user.created_at.isoformat() if user.created_at else None,
            "updated_at": user.updated_at.isoformat() if user.updated_at else None,
            "tenant_id": user.tenant_id
        }

    async def delete_user(
        self, db: AsyncSession, *, user_id: int, tenant_id: str
    ) -> bool:
        """软删除用户"""
        user = await self.get(db, id=user_id)
        if not user or user.tenant_id != tenant_id:
            return False

        user.is_deleted = True
        user.deleted_at = func.now()
        db.add(user)
        await db.commit()
        return True

    async def update_user_status(
        self, db: AsyncSession, *, user_id: int, status: bool, tenant_id: str
    ) -> bool:
        """更新用户状态"""
        user = await self.get(db, id=user_id)
        if not user or user.tenant_id != tenant_id:
            return False

        user.status = status
        user.is_active = status
        db.add(user)
        await db.commit()
        return True

    async def batch_delete_users(
        self, db: AsyncSession, *, user_ids: List[int], tenant_id: str
    ) -> int:
        """批量删除用户"""
        from sqlalchemy import update

        # 更新用户为删除状态
        stmt = update(User).where(
            and_(
                User.id.in_(user_ids),
                User.tenant_id == tenant_id,
                User.is_deleted == False
            )
        ).values(
            is_deleted=True,
            deleted_at=func.now()
        )

        result = await db.execute(stmt)
        await db.commit()
        return result.rowcount

    async def get_users_for_export(
        self, db: AsyncSession, *, tenant_id: str, filters: dict = None
    ) -> List[dict]:
        """获取用于导出的用户数据"""
        from sqlalchemy import select, and_, or_

        # 构建查询
        query = select(User).options(
            selectinload(User.dept),
            selectinload(User.roles)
        ).where(
            and_(
                User.tenant_id == tenant_id,
                User.is_deleted == False
            )
        )

        # 应用过滤条件
        if filters:
            if filters.get('keyword'):
                keyword = f"%{filters['keyword']}%"
                query = query.where(
                    or_(
                        User.username.like(keyword),
                        User.nickname.like(keyword),
                        User.email.like(keyword)
                    )
                )

            if filters.get('dept_id') is not None:
                query = query.where(User.dept_id == filters['dept_id'])

            if filters.get('status') is not None:
                query = query.where(User.status == filters['status'])

            if filters.get('user_ids'):
                query = query.where(User.id.in_(filters['user_ids']))

        query = query.order_by(User.created_at.desc())
        result = await db.execute(query)
        users = result.scalars().all()

        # 转换为导出格式
        export_data = []
        for user in users:
            export_data.append({
                "id": user.id,
                "username": user.username,
                "nickname": user.nickname or "",
                "email": user.email,
                "phone": user.phone or "",
                "gender": user.gender,
                "dept_name": user.dept.name if user.dept else "",
                "role_names": [role.name for role in user.roles] if user.roles else [],
                "status": user.status,
                "is_superuser": user.is_superuser,
                "created_at": user.created_at.strftime("%Y-%m-%d %H:%M:%S") if user.created_at else "",
                "last_login_time": user.last_login_time or ""
            })

        return export_data

    async def get_user_menu_permissions(
        self, db: AsyncSession, *, user_id: int, tenant_id: str
    ):
        """获取用户的菜单权限列表"""
        # 获取用户信息，包含角色
        user = await self.get_with_roles(db, user_id=user_id, tenant_id=tenant_id)
        if not user:
            return []

        # 如果是超级管理员，返回所有菜单权限
        if user.is_superuser:
            query = select(Permission).where(
                and_(
                    Permission.tenant_id == tenant_id,
                    Permission.is_deleted == False,
                    Permission.status == True,
                    Permission.visible == True,
                    Permission.type.in_(['M', 'C'])  # M-目录, C-菜单
                )
            ).order_by(Permission.sort.asc())
            result = await db.execute(query)
            return result.scalars().all()

        # 获取用户角色ID列表
        role_ids = [role.id for role in user.roles]
        if not role_ids:
            return []

        # 查询角色菜单权限
        query = (
            select(Permission)
            .join(
                role_permission_association,
                Permission.id == role_permission_association.c.permission_id
            )
            .where(
                and_(
                    role_permission_association.c.role_id.in_(role_ids),
                    role_permission_association.c.tenant_id == tenant_id,
                    Permission.tenant_id == tenant_id,
                    Permission.is_deleted == False,
                    Permission.status == True,
                    Permission.visible == True,
                    Permission.type.in_(['M', 'C'])  # M-目录, C-菜单
                )
            )
            .distinct()
            .order_by(Permission.sort.asc())
        )

        result = await db.execute(query)
        return result.scalars().all()

    async def change_password(
        self, db: AsyncSession, *, user: User, old_password: str, new_password: str
    ) -> bool:
        """修改用户密码"""
        if not verify_password(old_password, user.password):
            return False

        user.password = get_password_hash(new_password)
        db.add(user)
        await db.commit()
        return True

    async def reset_password(
        self, db: AsyncSession, *, user: User, new_password: str
    ) -> User:
        """重置用户密码"""
        user.password = get_password_hash(new_password)
        db.add(user)
        await db.commit()
        await db.refresh(user)
        return user

    async def update_password(
        self, db: AsyncSession, *, user_id: int, password: str
    ) -> User:
        """更新用户密码"""
        user = await self.get(db, id=user_id)
        if not user:
            raise ValueError("用户不存在")

        user.password = get_password_hash(password)
        db.add(user)
        await db.commit()
        await db.refresh(user)
        return user

    async def update_status(
        self, db: AsyncSession, *, user: User, status: bool
    ) -> User:
        """更新用户状态"""
        user.status = status
        user.is_active = status
        db.add(user)
        await db.commit()
        await db.refresh(user)
        return user

    async def search_users(
        self,
        db: AsyncSession,
        *,
        keyword: str,
        tenant_id: str,
        skip: int = 0,
        limit: int = 20,
    ) -> List[User]:
        """搜索用户"""
        query = select(User).where(
            and_(
                User.tenant_id == tenant_id,
                User.is_deleted == False,
                or_(
                    User.username.like(f"%{keyword}%"),
                    User.nickname.like(f"%{keyword}%"),
                    User.email.like(f"%{keyword}%"),
                )
            )
        ).offset(skip).limit(limit)

        result = await db.execute(query)
        return result.scalars().all()


# 创建用户服务实例
user_service = UserService()